 --EXEC sp_lot_trace_by_lot_number_by_type 'B87257','ggg'


ALTER  proc sp_lot_trace_by_lot_number_by_type
 @fab_lot_number 	VARCHAR(50),
 @lot_Type   		VARCHAR(50)
AS
BEGIN
 
 	DECLARE @results Table( Lot_ID 		bigint,
				Fab_Lot_Number 	VARCHAR(50),
 				Lot_Number 	VARCHAR(50),
 				Part_Number 	VARCHAR(50),
 				PS_Type 	VARCHAR(50),
 				PS_Type_Name 	VARCHAR(50),
 				VENDor_Name 	VARCHAR(50),
 				Wafer_Qty_In 	INT,
 				Wafer_Qty_Out 	INT,
 				Good_Die 	INT,
 				Qty_In 		INT,
 				Qty_Out 	INT,
 				Yield 		float,
 				Date_In 	datetime,
 				Date_Out 	datetime,
 				Ship_To_Location nVARCHAR(100),
				Test_Program	VARCHAR(50),
				Lot_Category	VARCHAR(20),
				Lot_Status	VARCHAR(50),
				Hold_Reason	VARCHAR(50),
 				Lot_Comments 	VARCHAR(500),
				Date_code   varchar(50),
				Lot_Split   INT,
				[User_Name] VARCHAR(50),
				Bom_Lot_No	VARCHAR(100),
				BOM_Qty     VARCHAR(50),
				Ps_Type_Id 	INT ,
				CUST_NAME VARCHAR(100),
				SALES_ORDER_NO VARCHAR(50),
				CUST_SHIP_DATE DATETIME,
				CUST_SHIP_QTY  INT )
 
 	DECLARE @LOT_TRACE_TABLE TABLE (Lot_ID 			INT, 
 					Lot_Number 		VARCHAR(50),
					Fab_Lot_Number 		VARCHAR(50),  
 					ProcessStep_ID  	INT, 
 					Location_ID 		INT, 
 					Ps_Type_Name 		VARCHAR(50), 
 					Lot_Comments 		VARCHAR(300), 
 					Lot_Current_Status 	VARCHAR(20),
					Lot_Category		VARCHAR(20), 	 
 					TimeStamp 	   	DATETIME )
 
 	DECLARE @lot_id_cursor 	 CURSOR
 	DECLARE @shipped_cursor  CURSOR
	DECLARE @Assembly_cursor  CURSOR
	DECLARE @Sales_cursor	CURSOR
	DECLARE @Assembly_Lot_Id INT
	DECLARE @assembly_qty_in INT
	DECLARE @index		 INT
	DECLARE @process_step_id BIGINT
	DECLARE @lot_id      INT
 	DECLARE @location_id INT
 	DECLARE @qty_in 	 INT
 	DECLARE @qty_out 	 INT
 	DECLARE @wafer_qty_in	INT
 	DECLARE @wafer_qty_out 	INT
 	DECLARE @good_die  		INT
	DECLARE @Ps_type_Id     INT
	DECLARE @fg_qty_out 	INT
 	DECLARE @ship_qty 		INT
	DECLARE @vendor_name 	VARCHAR(50)
 	DECLARE @temp_str		VARCHAR(50)
	DECLARE @ps_type_name 	VARCHAR(50)
 	DECLARE @ps_type 		VARCHAR(50)
	DECLARE @lot_category	VARCHAR(20)
	DECLARE @fab_lot		VARCHAR(50)
 	DECLARE @test_progarm	VARCHAR(50)
	DECLARE @part_number 	VARCHAR(50)	
 	DECLARE @lot_number 	VARCHAR(50)
 	DECLARE @lot_comments 	VARCHAR(500)
 	DECLARE @lot_curr_status  VARCHAR(20)
	DECLARE @hold_reason	VARCHAR(50)
	DECLARE @date_in 	 DATETIME
 	DECLARE @date_out 	 DATETIME
 	DECLARE @actual_ship_date DATETIME
	DECLARE @assembly_date_in DATETIME
	DECLARE @yield 		 FLOAT
	DECLARE @next_location   NVARCHAR(100)
 	DECLARE @DATE_CODE		VARCHAR(50)
	DECLARE @CUST_SHIP_LOT_NO VARCHAR(50),@CUST_SHIP_FAB_LOTNO VARCHAR(50), @CUSTMER_NAME 	VARCHAR(100), @SALES_ORDER_NO VARCHAR(50), @CUST_SHIP_DATE DATETIME, @CUST_SHIP_QTY INT
	
 	SET @index = charindex('.', @fab_lot_number)

	IF @index <> 0
 	BEGIN
 		SET @temp_str = substring(@fab_lot_number, 1 , @index )
	END
 
 	IF @temp_str IS NULL
 	BEGIN
 		SET @temp_str = @fab_lot_number
 	END 
 
 	INSERT INTO @LOT_TRACE_TABLE
 	SELECT Lot_ID, Lot_Number, Fab_Lot_Number, LT.ProcessStep_ID, Location_ID, Ps_Type_Name, Lot_Comments, Lot_Current_Status, Lot_Category, LT.TIMESTAMP
 	FROM LOT_TABLE LT, PROCESS_STEP PS WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND FAB_LOT_NUMBER LIKE @temp_str + '%'
	AND PS_TYPE_NAME IN ('FAB','WAFER STORE','SORT','Die Bank','Assembly','Test', 'Mark & Pack' ,'Class Store','Finished Goods') ORDER BY PS_TYPE_NAME, Date_In, FAB_LOT_NUMBER
 	
 	--SELECT *FROM @LOT_TRACE_TABLE
	
 	SET @lot_id_cursor = CURSOR FOR
 	SELECT Lot_ID, Lot_Number, Fab_Lot_Number, ProcessStep_ID, Location_ID, Ps_Type_Name, Lot_Comments, Lot_Current_Status, Lot_Category FROM @LOT_TRACE_TABLE ORDER BY TIMESTAMP
 	
 	OPEN @lot_id_cursor
	FETCH NEXT FROM @lot_id_cursor INTO @lot_id, @lot_number, @fab_lot, @process_step_id, @location_id, @ps_type_name, @lot_comments, @lot_curr_status, @lot_category
 		
 	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @ps_type = PS_Type,  @part_number = Part_Number FROM Product, Process_Step WHERE Process_Step.ProcessStep_ID = @process_step_id AND Process_Step.ProcessStep_ID = Product.ProcessStep_ID
 		SELECT @vendor_name = V_Name FROM VENDor, Location WHERE Location_ID = @location_id AND Location.VENDor_ID = VENDor.VENDor_ID
 			
		SET @Ps_type_Id = 
			CASE 
				WHEN @ps_type ='FAB' THEN 1
				WHEN @ps_type ='Wafer Store' THEN 2	
				WHEN @ps_type ='Sort' THEN 3
				WHEN @ps_type ='Die Bank' THEN 4
				WHEN @ps_type ='Assembly' THEN 5
				WHEN @ps_type ='FT' THEN 6
				WHEN @ps_type ='Bake & Dry' THEN 7
				WHEN @ps_type ='Finished Goods' OR @ps_type ='FG' THEN 8
				ELSE 0
			END
 			
		IF @ps_type = 'FAB'
		BEGIN
			
			SELECT TOP 1 @qty_in = Wafer_Qty_In, @date_in = Date_In, @qty_out = Wafer_Qty_Out, @date_out = Date_Out FROM Lot_Table_History WHERE Lot_ID = @lot_id ORDER BY TIMESTAMP DESC--AND Wafer_Qty_In = ( SELECT MAX( Wafer_Qty_In ) FROM Lot_Table_History WHERE Lot_id = @lot_id )
			 					
			IF( @qty_in IS NULL OR @qty_in = 0 )				
			BEGIN
				SELECT @qty_in = Wafer_Qty_In, @date_in = Date_In, @qty_out = Wafer_Qty_Out, @date_out = Date_Out FROM Lot_Table WHERE  Lot_ID = @lot_id
			END
			
			SELECT @qty_out = Ship_Qty, @date_out = ISNULL( SL.Ship_Date, SL.TimeStamp ), @next_location = SL_NextLocation FROM  Shipped_Lot SL WHERE lot_id= @lot_id 
			
			IF( @qty_out IS NULL OR @qty_out = 0 )				
			BEGIN	
					SELECT  @qty_out = Wafer_Qty_In, @date_out = Date_In FROM Lot_Table LT, Process_Step PS WHERE  LT.Processstep_ID = PS.Processstep_ID and Ps_Type_Name = 'WAFER STORE' AND Fab_Lot_Number = @fab_lot
					IF( @qty_out IS NULL OR @qty_out = 0 )				
					BEGIN						
						SELECT @date_out = Date_In, @qty_out = Wafer_Qty_In FROM Lot_Table LT, Process_Step PS WHERE  LT.Processstep_ID = PS.Processstep_ID and Ps_Type_Name = 'Sort' AND Fab_Lot_Number = @fab_lot
					END
			END 
					 					
			IF ( @lot_curr_status = 'close' AND ( @qty_out = 0 OR @qty_out IS NULL ) )
			BEGIN
				SET @qty_out = @qty_in
				SET @date_out = @date_in				
			END
	
			IF( @qty_in > 0 )
			BEGIN
				
				INSERT @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
					VALUES( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(VARCHAR, @date_in, 101), convert(VARCHAR, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id, @next_location,@test_progarm, @lot_curr_status, @hold_reason  )
			END
		END

		IF @ps_type = 'WAFER STORE' 
		BEGIN
			SELECT TOP 1 @qty_in = Wafer_Qty_In, @date_in = Date_In, @qty_out = Wafer_Qty_Out, @date_out = Date_Out FROM Lot_Table_History WHERE Lot_ID = @lot_id AND Wafer_Qty_In = ( SELECT MAX( Wafer_Qty_In ) FROM Lot_Table_History WHERE Lot_id = @lot_id )
			 					
			IF( @qty_in IS NULL OR @qty_in = 0 )				
			BEGIN
				SELECT @qty_in = Wafer_Qty_In, @date_in = Date_In, @qty_out = Wafer_Qty_Out, @date_out = Date_Out FROM Lot_Table WHERE Lot_ID = @lot_id
			END
			
			SELECT @date_out = Date_In, @qty_out = Wafer_Qty_In FROM Lot_Table_History LTH, Process_Step PS WHERE  LTH.Processstep_ID = PS.Processstep_ID AND Ps_Type_Name = 'Sort' AND Fab_Lot_Number = @fab_lot ORDER BY LTH.TIMESTAMP DESC

			IF( @qty_out IS NULL OR @qty_out = 0 )				
			BEGIN
				SELECT @date_out = Date_In, @qty_out = Wafer_Qty_In FROM Lot_Table LT, Process_Step PS WHERE  LT.Processstep_ID = PS.Processstep_ID and Ps_Type_Name = 'Sort' AND Fab_Lot_Number = @fab_lot
			END
			
			IF ( @lot_curr_status = 'close' and (@qty_out = 0 or @qty_out is null) )
			BEGIN
					SET @qty_out = @qty_in
					SET @date_out = @date_in	
			END	
						 				
			IF ( @qty_in IS NOT NULL AND @qty_in > 0 )
			BEGIN
				INSERT @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
					VALUES( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(VARCHAR, @date_in, 101), convert(VARCHAR, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id, @next_location,@test_progarm, @lot_curr_status, @hold_reason  )
			END
					
		END			

		IF @ps_type = 'Sort'
		BEGIN
			SELECT TOP 1 @qty_in = Wafer_Qty_In, @date_in = Date_In, @qty_out = Wafer_Qty_Out, @date_out = Date_Out FROM Lot_Table_History WHERE Lot_ID = @lot_id AND Wafer_Qty_In = ( SELECT MAX( Wafer_Qty_In ) FROM Lot_Table_History WHERE Lot_id = @lot_id )
				
			IF( @qty_in IS NULL OR @qty_in = 0 )				
			BEGIN
				SELECT @qty_in = Wafer_Qty_In, @date_in = Date_In, @qty_out = Wafer_Qty_Out, @date_out = Date_Out FROM Lot_Table WHERE Lot_ID = @lot_id 
			END
			ELSE
			BEGIN
					SELECT  @qty_out = Wafer_Qty_Out, @date_out = Date_Out FROM Lot_Table WHERE  Lot_ID = @lot_id 
			END
			
			SELECT @test_progarm = SL_TEST_PROGRAM FROM SORT_LOT WHERE LOT_ID = @LOT_ID
			
			IF @date_out IS NOT NULL AND (@qty_out IS NULL OR @qty_out = 0 )
			BEGIN
					SELECT @qty_out = COUNT(*), @good_die = SUM(BIN_1) FROM INFORMATIVE_BINNING WHERE LOT_ID = @lot_Id
			END

			IF( @qty_out IS NULL OR @qty_out = 0 )				
			BEGIN
				SELECT @qty_out = Ship_Qty, @date_out = ISNULL( SL.Ship_Date, SL.TimeStamp ), @next_location = SL_NextLocation, @good_die = Ship_Good_Die_Qty FROM  Shipped_Lot SL WHERE  lot_id= @lot_id
			END										 					
									
			IF(@lot_curr_status = 'close' AND (@qty_out IS NULL OR @qty_out = 0 ) )
			BEGIN
				SELECT @qty_out = SUM(Qty_In), @date_out = MAX(Date_In) FROM Lot_Table WHERE Fab_Lot_Number LIKE @fab_lot
			END

			-- SELECT * FROM SCRAPPED_LOTS
			IF ( ( SELECT COUNT(*) FROM SCRAPPED_LOTS SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.LOT_ID = @lot_id ) > 0 )
			BEGIN
				SELECT @qty_in = SL_SCRAPPED_WAFER_QTY, @date_in = SL_SCRAPPED_DATE, @lot_comments = SL_COMMENTS FROM SCRAPPED_LOTS SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.LOT_ID = @lot_id
				
				INSERT @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id)
					VALUES( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @qty_in, @qty_out, @yield, convert(VARCHAR, @date_in, 101), convert(VARCHAR, @date_out, 101), @lot_category, 'Moved To Scrap' ,@Ps_type_Id )
			END
			ELSE
			BEGIN
				IF( @qty_in > 0 )
				BEGIN
					INSERT @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
					VALUES( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(VARCHAR, @date_in, 101), convert(VARCHAR, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id, @next_location,@test_progarm, @lot_curr_status, @hold_reason  )
				END
			END
		END
			
		IF @ps_type = 'Die Bank'
		BEGIN
			SELECT TOP 1 @qty_in = Qty_In, @date_in = Date_In FROM Lot_Table_History WHERE Lot_ID = @lot_id AND Qty_In = ( SELECT MAX( Qty_In ) FROM Lot_Table_History WHERE Lot_id = @lot_id )
			
			IF( @qty_in IS NULL OR @qty_in = 0 ) 
			BEGIN
				SELECT @qty_in = Qty_In, @date_in = Date_In , @date_out = Date_Out, @qty_out = Qty_Out FROM Lot_Table WHERE Lot_ID = @lot_id					
			END

			IF @lot_curr_status = 'CLOSE'
			BEGIN
				SET @Assembly_cursor = CURSOR FOR SELECT LOT_ID FROM Lot_Table LT, Process_Step PS WHERE LT.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_type = 'Assembly' AND Lt.Fab_lot_Number = @fab_lot
				OPEN @Assembly_cursor
				FETCH NEXT FROM @Assembly_cursor INTO @Assembly_Lot_Id
				WHILE @@FETCH_STATUS = 0
				BEGIN
					SELECT TOP 1 @assembly_qty_in = Qty_In, @assembly_date_in = Date_In FROM Lot_Table_history WHERE Lot_ID = @Assembly_Lot_Id and Qty_In = ( SELECT Max( Qty_In ) FROM Lot_Table_History WHERE Lot_id = @Assembly_Lot_Id )
				
					IF( @assembly_qty_in IS NULL OR @assembly_qty_in = 0 )
					BEGIN
						SELECT TOP 1 @assembly_qty_in = Qty_In, @assembly_date_in = Date_In FROM Lot_Table WHERE Lot_ID = @Assembly_Lot_Id
					END

					IF ( @assembly_qty_in IS NOT NULL AND @assembly_qty_in > 0 )
					BEGIN
						SET @qty_out = ISNULL( @qty_out, 0 ) + @assembly_qty_in
						IF @date_out IS NULL
						BEGIN
							SET @date_out = @assembly_date_in
						END
						ELSE
						BEGIN
							IF @date_out > @assembly_date_in
							BEGIN
								SET @date_out = @assembly_date_in
							END
						END
					END
					FETCH NEXT FROM @Assembly_cursor INTO @Assembly_Lot_Id
				END
				CLOSE @Assembly_cursor
				DEALLOCATE @Assembly_cursor
			END								 				
			ELSE
			BEGIN
				SET @qty_out = 0
				SET @date_out = NULL
			END
				
			IF ( @qty_in is not null AND @qty_in > 0 )
			BEGIN

				IF @qty_in < @qty_out
				BEGIN
					SET @qty_out = @qty_in
				END
				INSERT @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
					VALUES( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(VARCHAR, @date_in, 101), convert(VARCHAR, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id, @next_location,@test_progarm, @lot_curr_status, @hold_reason  )
			END
		END	
		IF @ps_type = 'Assembly' 
		BEGIN
			
			SELECT TOP 1 @qty_in = Qty_In, @qty_out = Qty_Out, @date_in = Date_In, @date_out = Date_Out  FROM Lot_Table_history WHERE  Lot_ID = @lot_id and Qty_In = ( Select Max( Qty_In ) FROM Lot_Table_History WHERE  Lot_id = @lot_id )
				
			IF( @qty_in IS NULL OR @qty_in = 0 )				
			BEGIN
				SELECT @qty_in = Qty_In, @date_in = Date_In, @qty_out = Qty_Out, @date_out = Date_Out FROM Lot_Table WHERE Lot_ID = @lot_id 
			END

			IF @lot_curr_status = 'CLOSE'
			BEGIN
				SET @Assembly_cursor = CURSOR FOR SELECT LOT_ID FROM Lot_Table LT, Process_Step PS WHERE LT.ProcessStep_ID = PS.ProcessStep_ID AND Ps_Type_Name = 'Test' AND PS_TYPE = 'FT' AND Lt.Fab_lot_Number = @fab_lot AND Date_In > @date_in
				OPEN @Assembly_cursor
				FETCH NEXT FROM @Assembly_cursor INTO @Assembly_Lot_Id
				WHILE @@FETCH_STATUS = 0
				BEGIN
					SELECT TOP 1 @assembly_qty_in = Qty_In, @assembly_date_in = Date_In FROM Lot_Table_history WHERE Lot_ID = @Assembly_Lot_Id and Qty_In = ( SELECT Max( Qty_In ) FROM Lot_Table_History WHERE Lot_id = @Assembly_Lot_Id )
				
					IF( @assembly_qty_in IS NULL OR @assembly_qty_in = 0 )
					BEGIN
						SELECT TOP 1 @assembly_qty_in = Qty_In, @assembly_date_in = Date_In FROM Lot_Table WHERE Lot_ID = @Assembly_Lot_Id
					END

					IF ( @assembly_qty_in IS NOT NULL AND @assembly_qty_in > 0 )
					BEGIN
						SET @qty_out = ISNULL( @qty_out, 0 ) + @assembly_qty_in
						IF @date_out IS NULL
						BEGIN
							SET @date_out = @assembly_date_in
						END
						ELSE
						BEGIN
							IF @date_out > @assembly_date_in
							BEGIN
								SET @date_out = @assembly_date_in
							END
						END
					END

					FETCH NEXT FROM @Assembly_cursor INTO @Assembly_Lot_Id
				END
				CLOSE @Assembly_cursor
				DEALLOCATE @Assembly_cursor
			END
			ELSE 
			BEGIN
				SET @qty_out = 0
				SET @date_out = 0
			END		

			IF ( ( SELECT COUNT(*) FROM SCRAPPED_LOTS SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.LOT_ID = @lot_id ) > 0 )
			BEGIN
				SELECT @qty_in = SL_SCRAPPED_Die_QTY, @date_in = SL_SCRAPPED_DATE, @lot_comments = SL_COMMENTS FROM SCRAPPED_LOTS SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.LOT_ID = @lot_id
				
				INSERT @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id)
					values( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @qty_in, @qty_out, @yield, convert(VARCHAR, @date_in, 101), convert(VARCHAR, @date_out, 101), @lot_category, 'Moved To Scrap',@Ps_type_Id )
			END
			ELSE
			BEGIN
				IF( @qty_in > 0 )
				BEGIN						
					INSERT @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
					values( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(VARCHAR, @date_in, 101), convert(VARCHAR, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id, @next_location,@test_progarm, @lot_curr_status, @hold_reason  )
				END
			END
		END

		IF @ps_type = 'FT' 
		BEGIN
			SELECT TOP 1 @qty_in = Qty_In, @qty_out = Qty_Out, @date_in = Date_In, @date_out = Date_Out  FROM Lot_Table_history WHERE  Lot_ID = @lot_id and Qty_In = ( Select Max( Qty_In ) FROM Lot_Table_History WHERE  Lot_id = @lot_id )
			--SELECT @qty_in = Qty_In, @qty_out = Qty_Out, @date_in = Date_In, @date_out = Date_Out  FROM Lot_Table WHERE  Lot_ID = @lot_id
				
			IF( @qty_in IS NULL OR @qty_in = 0 )				
			BEGIN
				SELECT @qty_in = Qty_In, @date_in = Date_In, @qty_out = Qty_Out, @date_out = Date_Out  FROM Lot_Table WHERE  Lot_ID = @lot_id order by timestamp desc
			END
			IF @lot_curr_status = 'CLOSE'
			BEGIN
				IF(@qty_out is null OR @qty_out = 0)
				BEGIN				
					Select @date_out = MAX(Date_In), @qty_out = SUM(Qty_In) From Lot_Table LT, Process_Step PS WHERE  LT.Processstep_ID = PS.Processstep_ID and Ps_Type_Name In( 'Class Store', 'Mark & Pack') AND Fab_Lot_Number = @fab_lot AND Date_In >= @date_In 

					IF( @qty_out is null OR @qty_out = 0 )
					BEGIN
						SELECT @qty_out = Qty_In, @date_out = Date_In, @fg_qty_out = Qty_Out FROM Lot_Table LT, Process_Step PS WHERE Lt.ProcessStep_Id = PS.ProcessStep_Id AND PS_Type = 'FG' AND Lot_Number = @lot_number AND Date_In >= @date_In --AND Lot_current_Status = 'Running'
						SELECT @ship_qty = SUM(CSLNT_QTY_PULL) FROM CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, Lot_Table LT, Process_Step PS WHERE CSLNT.Lot_ID = LT.Lot_Id AND Lt.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type = 'FG' AND CSLNT.CSLNT_STATUS = 'SHIPPED' AND Lot_Number = @lot_number  AND Date_In >= @date_In
						SET @qty_out = ISNULL( @qty_out, 0) + ISNULL( @ship_qty, 0) + ISNULL( @fg_qty_out, 0)
					END	

					IF( @qty_out is null OR @qty_out = 0 )
					BEGIN
						SELECT @qty_out = Qty_In, @date_out = Date_In, @fg_qty_out = Qty_Out FROM Lot_Table Lt, Process_Step PS WHERE Lt.ProcessStep_Id = PS.ProcessStep_Id AND PS_Type = 'FG' AND Fab_Lot_Number = @fab_lot AND Lot_current_Status = 'Running' AND Date_In >= @date_In
						SELECT @ship_qty = SUM(CSLNT_QTY_PULL) FROM CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, Lot_Table LT, Process_Step PS WHERE CSLNT.Lot_ID = LT.Lot_Id AND Lt.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type = 'FG' AND CSLNT.CSLNT_STATUS = 'SHIPPED' AND Fab_Lot_Number = @fab_lot  AND Date_In >= @date_In
						SET @qty_out = ISNULL( @qty_out, 0) + ISNULL( @ship_qty, 0) + ISNULL( @fg_qty_out, 0)
					END	
				END	
			END
			ELSE
			BEGIN
				SET @qty_out = 0
				SET @date_out = null
			END

			IF ( ( SELECT COUNT(*) FROM SCRAPPED_LOTS SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.LOT_ID = @lot_id ) > 0 )
			BEGIN
				SELECT @qty_in = SL_SCRAPPED_Die_QTY, @date_in = SL_SCRAPPED_DATE, @lot_comments = SL_COMMENTS FROM SCRAPPED_LOTS SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.LOT_ID = @lot_id
				
				INSERT @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id)
					VALUES( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @qty_in, @qty_out, @yield, convert(VARCHAR, @date_in, 101), convert(VARCHAR, @date_out, 101), @lot_category, 'Moved To Scrap',@Ps_type_Id )
			END
			ELSE
			BEGIN
				IF( @qty_in > 0 )
				BEGIN
					INSERT @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
					VALUES( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(VARCHAR, @date_in, 101), convert(VARCHAR, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id, @next_location,@test_progarm, @lot_curr_status, @hold_reason  )
				END
			END
		END

		IF @ps_type = 'Bake & Dry' 
		BEGIN
			SELECT TOP 1 @qty_in = Qty_In, @qty_out = Qty_Out, @date_in = Date_In, @date_out = Date_Out  FROM Lot_Table_history WHERE  Lot_ID = @lot_id and Qty_In = ( Select Max( Qty_In ) FROM Lot_Table_History WHERE  Lot_id = @lot_id )
			 					
			IF( @qty_in IS NULL OR @qty_in = 0 )				
			BEGIN
				SELECT @qty_in = Qty_In, @date_in = Date_In, @qty_out = Qty_Out, @date_out = Date_Out  FROM Lot_Table WHERE  Lot_ID = @lot_id order by timestamp desc
			END

			IF( @qty_out is null OR @qty_out = 0 )
			BEGIN
				SELECT @qty_out = Qty_In, @date_out = Date_In, @fg_qty_out = Qty_Out FROM Lot_Table Lt, Process_Step PS WHERE Lt.ProcessStep_Id = PS.ProcessStep_Id AND PS_Type = 'FG' AND Lot_Number = @lot_number AND Date_In >= @date_In --AND Lot_current_Status = 'Running' 
				SELECT @ship_qty = SUM(CSLNT_QTY_PULL) FROM CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, Lot_Table LT, Process_Step PS WHERE CSLNT.Lot_ID = LT.Lot_Id AND Lt.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type = 'FG' AND CSLNT.CSLNT_STATUS = 'SHIPPED' AND Lot_Number = @lot_number  AND Date_In >= @date_In
				SET @qty_out = ISNULL( @qty_out, 0) + ISNULL( @ship_qty, 0) + ISNULL( @fg_qty_out, 0)
				
			END

			IF( @qty_out is null OR @qty_out = 0 )
			BEGIN
				SELECT @qty_out = Qty_In, @date_out = Date_In, @fg_qty_out = Qty_Out FROM Lot_Table Lt, Process_Step PS WHERE Lt.ProcessStep_Id = PS.ProcessStep_Id AND PS_Type = 'FG' AND  Fab_Lot_Number = @fab_lot AND Lot_current_Status = 'Running' AND Date_In >= @date_In
				SELECT @ship_qty = SUM(CSLNT_QTY_PULL) FROM CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, Lot_Table LT, Process_Step PS WHERE CSLNT.Lot_ID = LT.Lot_Id AND Lt.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type = 'FG' AND CSLNT.CSLNT_STATUS = 'SHIPPED' AND Fab_Lot_Number = @fab_lot  AND Date_In >= @date_In
				SET @qty_out = ISNULL( @qty_out, 0) + ISNULL( @ship_qty, 0) + ISNULL( @fg_qty_out, 0)
			END
			 								 				
			IF ( @qty_in is not null AND @qty_in > 0 )
			BEGIN
				INSERT @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
					values( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(VARCHAR, @date_in, 101), convert(VARCHAR, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id, @next_location,@test_progarm, @lot_curr_status, @hold_reason  )
			END
		END
		
		IF @ps_type = 'FG' OR @Ps_type = 'Finished Goods'
		BEGIN
			
			SELECT @qty_in = Qty_In, @date_in = Date_In , @date_out = Date_Out, @qty_out = Qty_Out FROM Lot_Table WHERE Lot_ID = @lot_id --AND Lot_current_Status = 'Running'
			
			SELECT @ship_qty = SUM(CSLNT_QTY_PULL) FROM CUSTOMER_SHIPPING_LOT_NUMBER_TABLE WHERE LOT_ID = @lot_id AND CSLNT_STATUS = 'SHIPPED'
			
			IF ( @qty_in IS NULL OR @qty_in = 0 ) 
			BEGIN
				SET  @qty_in = isNUll(@ship_qty,0) + isNull(@qty_out, 0)
			END
			ELSE
			BEGIN
				SET @qty_in = isNull(@qty_in,0) + isNull(@ship_qty,0)+ isNull(@qty_out,0)
			END

			SET @qty_out = isNull(@ship_qty,0) + isNull(@qty_out,0)
			
			/*if( @qty_in is null or @qty_in = 0 ) 
			BEGIN
				SELECT top 1 @qty_in = Qty_In, @date_in = Date_In FROM Lot_Table_History WHERE  Lot_ID = @lot_id order by timestamp desc
			END
			IF (@qty_out = 0 or @qty_out is null)
			BEGIN
				SELECT @qty_out = Ship_Qty, @date_out = ISNULL( SL.Ship_Date, SL.TimeStamp ), @next_location = SL_NextLocation FROM  Shipped_Lot SL WHERE  lot_id= @lot_id 
			END
				 					
			IF ( @lot_curr_status = 'close' and (@qty_out = 0 or @qty_out is null) )
			BEGIN
				SET @qty_out = @qty_in
				SET @date_out = @date_in				
			END
			*/							 				
			IF ( @qty_in is not null AND @qty_in > 0 )
			BEGIN
				
				INSERT @results ( Lot_ID, Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Good_Die, Qty_In,  Qty_Out, Yield, Date_In, Date_Out, Lot_Category, Lot_Comments, Ps_Type_Id, Ship_To_Location,Test_Program,Lot_Status, Hold_Reason )
					values( @lot_id, @fab_lot, @lot_number, @part_number, @ps_type, @ps_type_name, @vendor_name, @wafer_qty_in, @wafer_qty_out, @good_die, @qty_in, @qty_out, @yield, convert(VARCHAR, @date_in, 101), convert(VARCHAR, @date_out, 101),@lot_category, @lot_comments,@Ps_type_Id, @next_location,@test_progarm, @lot_curr_status, @hold_reason  )
			END
					
		END
		---FOR GETTING THE SALES ORDER , CUSTOMER INFO
		IF @ps_type = 'FG' OR @Ps_type = 'Finished Goods'
		BEGIN
			SET @Sales_cursor = CURSOR FOR SELECT LOT_NUMBER,FAB_LOT_NUMBER, C_NAME, SALESORDER_NO, CST_SHIP_DATE, SUM(CSLNT_QTY_PULL), LT.LOT_DATE_CODE  FROM CUSTOMER_SHIPPING_TABLE CST , CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLT, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, SALES_ORDER SO, CUSTOMER C, LOT_TABLE LT
				WHERE CSLT.CSLIT_ID= CSLIT.CSLIT_ID AND CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID AND CST.SALESORDER_ID = SO.SALESORDER_ID AND SO.CUSTOMER_ID = C.CUSTOMER_ID AND LT.LOT_ID = CSLT.LOT_ID AND CSLT.LOT_ID = @lot_id AND CST.CST_STATUS = 'SHIPPED' AND CSLNT_QTY_PULL > 0 GROUP BY LOT_NUMBER, FAB_LOT_NUMBER, C_NAME, SALESORDER_NO,CST_SHIP_DATE,LOT_DATE_CODE
			OPEN @Sales_cursor
			FETCH NEXT FROM @Sales_cursor INTO @CUST_SHIP_LOT_NO, @CUST_SHIP_FAB_LOTNO, @CUSTMER_NAME, @SALES_ORDER_NO, @CUST_SHIP_DATE,@CUST_SHIP_QTY , @DATE_CODE
			WHILE @@FETCH_STATUS = 0
			BEGIN
				INSERT @results ( Fab_Lot_Number,LOT_NUMBER, CUST_NAME, SALES_ORDER_NO, CUST_SHIP_DATE,CUST_SHIP_QTY, Ps_Type_Id,DATE_CODE)
					values( @CUST_SHIP_FAB_LOTNO,@CUST_SHIP_LOT_NO, @CUSTMER_NAME, @SALES_ORDER_NO, CONVERT(VARCHAR, @CUST_SHIP_DATE,101), @CUST_SHIP_QTY, 20, @DATE_CODE )

				SET @CUSTMER_NAME		= null
				SET @SALES_ORDER_NO		= null
				SET @CUST_SHIP_DATE     = NULL
				SET @CUST_SHIP_QTY		= NULL
				SET @CUST_SHIP_LOT_NO	= NULL
				SET @CUST_SHIP_FAB_LOTNO = NULL
				SET @DATE_CODE			 = null
				FETCH NEXT FROM @Sales_cursor INTO @CUST_SHIP_LOT_NO, @CUST_SHIP_FAB_LOTNO,@CUSTMER_NAME, @SALES_ORDER_NO, @CUST_SHIP_DATE,@CUST_SHIP_QTY , @DATE_CODE
			END
			CLOSE @Sales_cursor
			DEALLOCATE @Sales_cursor
 		END
  			
		SET @lot_id 		= null
		SET @process_step_id 	= null
		SET @location_id 	= null
		SET @vendor_name 	= null
		SET @ps_type_name 	= null
		SET @ps_type 		= null
		SET @qty_in 		= null
		SET @qty_out 		= null
		SET @date_in 		= null
		SET @date_out 		= null
		SET @wafer_qty_in 	= null
		SET @wafer_qty_out 	= null
		SET @good_die 		= null
		SET @part_number 	= null
		SET @lot_category	= null
		SET @lot_comments 	= null
		SET @lot_curr_status 	= null
		SET @Ps_type_Id	     	= null
		SET @hold_reason	= null
		SET @next_location	= null
		SET @test_progarm	= null
		SET @assembly_qty_in	= NULL
		SET @assembly_date_in   = NULL
		SET @ship_qty			= null	
		
		FETCH next FROM @lot_id_cursor INTO @lot_id, @lot_number,@fab_lot, @process_step_id, @location_id, @ps_type_name, @lot_comments, @lot_curr_status, @lot_category
	END

 	CLOSE @lot_id_cursor
 	DEALLOCATE @lot_id_cursor

	--SELECT * FROM @results

 	IF @lot_Type = 'fabLot'
	BEGIN
		SELECT LOT_ID, Fab_Lot_Number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name, SUM(Wafer_Qty_In) as Wafer_Qty_In,  SUM(Wafer_Qty_Out) as Wafer_Qty_Out, SUM(Qty_In) as Qty_In, Qty_Out as Qty_Out, Yield, min(Date_In) as Date_In , max(Date_Out) as Date_Out, ISNULL(Lot_Category, 'PROD') AS Lot_Category , Lot_Comments,Date_Code,ISNULL(Lot_Split,1) AS Lot_Split,[User_Name],Bom_Lot_No,Bom_Qty, Ps_Type_Id, Ship_To_Location,Test_Program,Lot_Status, Hold_Reason, Good_Die,CUST_NAME,SALES_ORDER_NO,CUST_SHIP_DATE,CUST_SHIP_QTY
			from @results group by LOT_ID,fab_lot_number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name,Yield, Lot_Category, Lot_Comments,Date_Code,Lot_Split,[User_Name],Bom_Lot_No,Bom_Qty, Ps_Type_Id, Ship_To_Location , Test_Program, Lot_Status, Hold_Reason, Good_Die, Qty_Out,CUST_NAME,SALES_ORDER_NO,CUST_SHIP_DATE,CUST_SHIP_QTY order by Ps_Type_Id, Fab_Lot_Number --,Lot_Number--,Date_In
	END
	ELSE --IF @lot_Type = 'vENDorLot'
	BEGIN
		SELECT LOT_ID,Fab_Lot_Number, Lot_number, Part_Number, PS_Type, PS_Type_Name, VENDor_Name, Wafer_Qty_In,  Wafer_Qty_Out, Qty_In, Qty_Out, Yield, Date_In, Date_Out, ISNULL(Lot_Category, 'PROD') AS Lot_Category, Lot_Comments,Date_Code,ISNULL(Lot_Split,1) AS Lot_Split,[User_Name],Bom_Lot_No,Bom_Qty, Ps_Type_Id, Ship_To_Location,Test_Program,Lot_Status, Hold_Reason, Good_Die, CUST_NAME,SALES_ORDER_NO,CUST_SHIP_DATE,CUST_SHIP_QTY 
			from @results order by Ps_Type_Id,Fab_Lot_Number, Lot_number--,Date_In
	END
	--SELECT * FROM @results order by Ps_Type_Id,Lot_Number--,Date_In
 	
 END


